using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class ShatterItems : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public DataTable GetDataForShatterForm(long nShatterID)
		{
			DataTable dataTable = new DataTable("ShatterItem");
			string cmdText = "select p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit AS 单位,p.ProductCode AS 编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',si.Quantity AS 补片数量, dbo.ItemSquare(p.Width,p.Height,si.Quantity) as 补片面积,si.Department as 工作组,si.Reason as 补片原因, c.CompanyName AS 客户,pj.ProjectName AS 计划 FROM ShatterItem AS si inner join Product AS p on si.ProductID=p.PKID inner join [Order] AS o on p.OrderID=o.PKID inner join Project AS pj on o.ProjectID=pj.PKID inner join Customer AS c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE si.ShatterID=@ShatterID order by si.Department,c.CompanyName,pj.ProjectName,convert( int, substring(p.PKID,1,8) ), CONVERT( INT, SUBSTRING(p.PKID,CHARINDEX('.',p.PKID)+1,CHARINDEX('-',p.PKID)-CHARINDEX('.',p.PKID)-1) ), CONVERT( INT, SUBSTRING(p.PKID,CHARINDEX('-',p.PKID)+1,LEN(p.PKID)) )";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@ShatterID"].Value = nShatterID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetShatterItems(long nShatterID)
		{
			DataTable dataTable = new DataTable("ShatterItem");
            //string cmdText = "select p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit AS 单位,p.ProductCode AS 编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',p.Quantity AS 数量,o.Remark+'  '+p.Remark AS 备注,dbo.GetTechnicsOfProductID(p.PKID) as 工艺,si.TransitionThickness as 补片层名称,si.Quantity AS 补片数量, dbo.ItemSquare(p.Width,p.Height,si.Quantity) as 补片面积,si.Department as 工作组,si.Reason as 补片原因, c.CompanyName AS 客户,pj.ProjectName AS 计划 FROM ShatterItem AS si inner join Product AS p on si.ProductID=p.PKID inner join [Order] AS o on p.OrderID=o.PKID inner join Project AS pj on o.ProjectID=pj.PKID inner join Customer AS c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE si.ShatterID=@ShatterID order by si.Department,c.CompanyName,pj.ProjectName,convert( int, substring(p.PKID,1,6) ), CONVERT( INT, SUBSTRING(p.PKID,CHARINDEX('.',p.PKID)+1,CHARINDEX('-',p.PKID)-CHARINDEX('.',p.PKID)-1) ), CONVERT( INT, SUBSTRING(p.PKID,CHARINDEX('-',p.PKID)+1,LEN(p.PKID)) )";
            string cmdText = @"SELECT p.PKID AS 产品ID, pt.[Name] AS 类型, pt.Unit AS 单位, p.ProductCode AS 编号, p.Width AS 宽
	                                , p.SubtenseOfWidth AS '宽(短边)', p.Height AS 高, p.SubtenseOfHeight AS '高(短边)', p.Quantity AS 数量
	                                , o.Remark + '  ' + p.Remark AS 备注
	                                , dbo.GetTechnicsOfProductID(p.PKID) AS 工艺, si.TransitionThickness AS 补片层名称, si.Quantity AS 补片数量
	                                , dbo.ItemSquare(p.Width, p.Height, si.Quantity) AS 补片面积
	                                , si.Department AS 工作组, si.Reason AS 补片原因, c.CompanyName AS 客户, pj.ProjectName AS 计划
                                FROM ShatterItem si
	                                INNER JOIN Product p ON si.ProductID = p.PKID
	                                INNER JOIN [Order] o ON p.OrderID = o.PKID
	                                INNER JOIN Project pj ON o.ProjectID = pj.PKID
	                                INNER JOIN Customer c ON pj.CustomerID = c.PKID
	                                INNER JOIN ProductType pt ON p.ProductTypeID = pt.PKID
                                WHERE si.ShatterID = @ShatterID
                                ORDER BY si.Department, c.CompanyName, pj.ProjectName, p.PKID";
            try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@ShatterID"].Value = nShatterID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable LoadShatterItem(long nShatterID, string sProductID)
		{
			DataTable dataTable = new DataTable("ShatterItem");
			string cmdText = "select p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit AS 单位,p.ProductCode AS 编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',p.Quantity AS 数量,p.Remark AS 备注,dbo.GetTechnicsOfProductID(p.PKID) as 工艺,si.TransitionThickness as 补片层名称,si.Quantity AS 补片数量, dbo.ItemSquare(p.Width,p.Height,si.Quantity) as 补片面积,si.Department as 工作组,si.Reason as 补片原因, c.CompanyName AS 客户,pj.ProjectName AS 计划 FROM ShatterItem AS si inner join Product AS p on si.ProductID=p.PKID inner join [Order] AS o on p.OrderID=o.PKID inner join Project AS pj on o.ProjectID=pj.PKID inner join Customer AS c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE si.ShatterID=@ShatterID and si.ProductID=@ProductID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterID", SqlDbType.BigInt));
				this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ShatterID"].Value = nShatterID;
				this.selectCommand.Parameters["@ProductID"].Value = sProductID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool InsertShatterItem(long nShatterID, string sProductID, string nTransitionThickness, int nQuantity, string sDepartment, string sReason, int nOperatorID)
		{
			try
			{
				if (this.insertCommand == null || this.insertCommand.Connection.State == ConnectionState.Closed)
				{
					this.insertCommand = new SqlCommand("InsertShatterItem", new SqlConnection(AppSettings.ConnectionString));
					this.insertCommand.CommandType = CommandType.StoredProcedure;
					this.insertCommand.Connection.Open();
					this.insertCommand.Parameters.Add(new SqlParameter("@ShatterID", SqlDbType.BigInt));
					this.insertCommand.Parameters.Add(new SqlParameter("@productID", SqlDbType.VarChar, 32));
					this.insertCommand.Parameters.Add(new SqlParameter("@TransitionThickness", SqlDbType.NVarChar, 64));
					this.insertCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int));
					this.insertCommand.Parameters.Add(new SqlParameter("@Department", SqlDbType.NVarChar, 32));
					this.insertCommand.Parameters.Add(new SqlParameter("@Reason", SqlDbType.NVarChar, 32));
					this.insertCommand.Parameters.Add(new SqlParameter("@operatorID", SqlDbType.Int));
				}
				this.insertCommand.Parameters["@ShatterID"].Value = nShatterID;
				this.insertCommand.Parameters["@productID"].Value = sProductID;
				this.insertCommand.Parameters["@TransitionThickness"].Value = nTransitionThickness;
				this.insertCommand.Parameters["@quantity"].Value = nQuantity;
				this.insertCommand.Parameters["@Department"].Value = sDepartment;
				this.insertCommand.Parameters["@Reason"].Value = sReason;
				this.insertCommand.Parameters["@operatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool DeleteShatterItem(long nShatterID, string sProductID, int nOperatorID)
		{
			try
			{
				if (this.deleteCommand == null || this.deleteCommand.Connection.State == ConnectionState.Closed)
				{
					this.deleteCommand = new SqlCommand("DeleteShatterItem", new SqlConnection(AppSettings.ConnectionString));
					this.deleteCommand.CommandType = CommandType.StoredProcedure;
					this.deleteCommand.Connection.Open();
					this.deleteCommand.Parameters.Add(new SqlParameter("@ShatterID", SqlDbType.BigInt));
					this.deleteCommand.Parameters.Add(new SqlParameter("@productID", SqlDbType.VarChar, 32));
					this.deleteCommand.Parameters.Add(new SqlParameter("@operatorID", SqlDbType.Int));
				}
				this.deleteCommand.Parameters["@ShatterID"].Value = nShatterID;
				this.deleteCommand.Parameters["@productID"].Value = sProductID;
				this.deleteCommand.Parameters["@operatorID"].Value = nOperatorID;
				this.deleteCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
